package koby99;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.driver.DatabaseError;
import oracle.jdbc.proxy.annotation.Pre;

public class J03_MemberDAO implements J03_IMemberDAO {

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:koby99/koby99@112.216.253.125:1521:xe";
	private String insertSQL = " insert into J03MEMBER values ( exam.nextval, ? , ? , ? , ? , sysdate ) ";
	private String SelectSQL = "select * from J03MEMBER order by mno desc"; 
	private String UpdateSQL = "update J03MEMBER set passwd = ?, name = ?, email = ?, regdate = sysdate where mno = ?"; 
	private String DeleteSQL = "delete J03MEMBER where mno = ?"; 
	
	// ? 는 넣는 값이 알수 없을때 적는 녀석이다.
	
	@Override
	public boolean insertMember(J03_MemberVO m) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
				// ? 매개변수에 대입할 값 지정 ( 몇번째 물음포, 대입할 값 )
				pstmt.setString( 1, m.getUserid() );
				pstmt.setString( 2, m.getPasswd() );
				pstmt.setString( 3, m.getName() );
				pstmt.setString( 4, m.getEmail() );
			pstmt.executeUpdate();
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{ close(conn, pstmt, null);
		}
		
		return result;
}// insertMember
/////////////////////
	private Connection openDatabase(){
		try{Class.forName(DRV);
		return DriverManager.getConnection(URL);
		}catch(Exception ex){
			ex.printStackTrace();
			
		}
		return null;
	}
	

	//JDBC 객체를 닫는 메서드
	private void close(
			Connection c, PreparedStatement p, ResultSet r){ 
		if (r != null) try { r.close(); r= null; } catch(Exception e) {}
		if (p != null) try { p.close(); p= null; } catch(Exception e) {}
		if (c != null) try { c.close(); c= null; } catch(Exception e) {}
	}
	
	// select 문으로 전체 회원정보를 조회한 후
	// MemberVO를 내용으로 가지는
	// 동적배열 List로 결과를 넘김
	@Override
	public List<J03_MemberVO> selectMember() {
		Connection conn =null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		List<J03_MemberVO> result = new ArrayList<>();
		try{
			conn = openDatabase();
			pstmt = conn.prepareStatement(SelectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				J03_MemberVO m = new J03_MemberVO();
				m.setMno(rs.getString("mno"));
				m.setUserid(rs.getString("userid"));
				m.setPasswd(rs.getString("passwd"));
				m.setName(rs.getString("name"));
				m.setEmail(rs.getString("email"));
				m.setRegdate(rs.getString("regdate"));
				result.add(m);
				
			}
			
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			close(conn, pstmt, rs);
		}
	
		return result;
	}
	@Override
	public boolean updateMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try{
			conn = openDatabase();
			pstmt = conn.prepareStatement(UpdateSQL);
				pstmt.setString(1,m.getPasswd());
				pstmt.setString(2,m.getName());
				pstmt.setString(3,m.getEmail());
				pstmt.setString(4,m.getMno());
			int cut = pstmt.executeUpdate(); //수정된 정보에 수를 알려주는 녀석임
			if ( cut == 0) System.out.println("명령은 실행됬지만, 수정된 데이터는 없음");
			
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			close(conn, pstmt, null);		}
		
		return result;
	}
	@Override
	public boolean deleteMember(J03_MemberVO m) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = openDatabase();
			pstmt = conn.prepareStatement(DeleteSQL);
				pstmt.setString(1, m.getMno());
			int cut = pstmt.executeUpdate(); //수정된 정보에 수를 알려주는 녀석임
			if ( cut == 0) System.out.println("명령은 실행됬지만, 수정된 데이터는 없음");
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			close(conn, pstmt, null);
		}
		return result;
	}
	

}
